﻿/**********************************弘石项目*******************************************
* 功能描述:考勤 加班 出勤 假日
* 作者:石锦涛
* 时间:2021-12-14
* 
**********************************弘石项目*******************************************/
using Dapper;
using HongStone.Domain.AttendanceManagement;
using HongStone.Domain.OperationsManagement;
using HongStone.Domain.SystemSetupModel.FrequentlyUsedDataModel;
using HongStone.Domain.SystemSetupModel.InstitutionSettingModel;
using HongStone.Dto;
using HongStone.Ince.IClient;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Z.Dapper.Plus;

namespace HongStone.Repository
{
    public class CheckAttendanceDal
    {
        private readonly HongStoneContext _hongStoneContext;
        private readonly ILogger<CheckAttendanceDal> _logger;

        private readonly IConfiguration _configuration;
        public CheckAttendanceDal(HongStoneContext hongStoneContext, IConfiguration configuration, ILogger<CheckAttendanceDal> logger)
        {
            _hongStoneContext = hongStoneContext;
            _configuration = configuration;
            _logger = logger;
            _logger.LogDebug(1, "NLog injected into HomeController");
        }

        /// <summary>
        /// 部门加班补助显示
        /// </summary>
        /// <param name="nian"></param>
        /// <param name="yue"></param>
        /// <returns></returns>
        public List<ShowSubsidyDto> showSubsidy(int? bum,string nian,string yue)
        {
            try
            {
                using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
                {
                    var query = "select a.SubsidyId,a.PzName,a.PzRank,c.StaffName,a.PMonthlyWages,a.PLeave,a.PBeLate,a.POvertime,a.PCoefficient,a.PCalculateMoney,a.TheQuota,a.PState,a.PDepartment,b.DeptName from DepartmentSubsidy a join Department b on a.PDepartment = b.DeptId join Staff c on a.PzName = c.StaffId where a.PzRank=1 ";
                        if(!string.IsNullOrEmpty(nian))
                        {
                          query += $" and SUBSTRING(CONVERT(Nvarchar(20),a.PDateTimes),1,4)='{nian}' ";
                         }
                       if (!string.IsNullOrEmpty(yue))
                        {
                        query += $" and SUBSTRING(CONVERT(Nvarchar(20),a.PDateTimes),6,2)='{yue}' ";
                        }
                       if(bum>0)
                       {
                        query += $" and a.PDepartment ='{bum}'";
                       }

                    var list = conn.Query<ShowSubsidyDto>(query).ToList();
                    return list;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 部门显示
        /// </summary>
        /// <returns></returns>
        public List<Department> ShowOPLa()
        {
            try
            {
                var query = _hongStoneContext.departments.ToList();
                _logger.LogInformation("显示成功");
                return query;
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 编辑修改补助
        /// </summary>
        /// <param name="subsidy"></param>
        /// <returns></returns>
        public int UpdateSubsidy(DepartmentSubsidy subsidy)
        {
            try
            {
               var list= _hongStoneContext.Entry(subsidy).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 部门经理加班补助显示
        /// </summary>
        /// <param name="nian"></param>
        /// <param name="yue"></param>
        /// <returns></returns>
        public List<ShowSubsidyDto> showJingliSubsidy(int? bum, string nian, string yue)
        {
            try
            {
                using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
                {
                    var query = "select a.SubsidyId,a.PzName,a.PzRank,c.StaffName,a.PMonthlyWages,a.PLeave,a.PBeLate,a.POvertime,a.PCoefficient,a.PCalculateMoney,a.TheQuota,a.PState,a.PDepartment,b.DeptName from DepartmentSubsidy a join Department b on a.PDepartment = b.DeptId join Staff c on a.PzName = c.StaffId where a.PzRank=2 ";
                    if (!string.IsNullOrEmpty(nian))
                    {
                        query += $" and SUBSTRING(CONVERT(Nvarchar(20),a.PDateTimes),1,4)='{nian}' ";
                    }
                    if (!string.IsNullOrEmpty(yue))
                    {
                        query += $" and SUBSTRING(CONVERT(Nvarchar(20),a.PDateTimes),6,2)='{yue}' ";
                    }
                    if (bum > 0)
                    {
                        query += $" and a.PDepartment ='{bum}'";
                    }

                    var list = conn.Query<ShowSubsidyDto>(query).ToList();
                    return list;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 显示添加经理页面
        /// </summary>
        /// <returns></returns>
        public List<ManagerMemberDto> showManager()
        {
            try
            {
               using(var conn=new SqlConnection(_configuration.GetConnectionString("MSSQL")))
                {
                    var list = "select a.DeptId as bian,b.KDepartmentId as zhu,b.KDepartmentManagerId as jli,d.KDepartmentIdzhong as zzhu,d.KalwaysManagerId as zjil,a.DeptName as bumen,c.StaffName as jinli,f.StaffName as Zhongjinli from Department a left join DepartmentManagerSet b on a.DeptId=b.KDepartmentNameId left join Staff c on b.KDepartmentManagerId = c.StaffId  left join  DepartmentManagerZhong d  on d.KDepartmentNameIdzhong = a.DeptId left join Staff f on d.KalwaysManagerId = f.StaffId";

                   var query = conn.Query<ManagerMemberDto>(list).ToList();
                    return query;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 查找人员
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pum"></param>
        /// <returns></returns>
        public List<ManagerMemberDto> AddManger(string name,int? pum)
        {
            try
            {
                using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
                {
                    var query = " select a.StaffId,a.StaffName,a.DeptId, b.DeptName from Staff a join Department b on a.DeptId=b.DeptId  where 1=1 ";
                    if (!string.IsNullOrEmpty(name))
                    {
                        query += $"  and a.StaffName like '%{name}%'";
                    }
                    if (pum > 0)
                    {
                        query += $"  and a.DeptId='{pum}'";
                    }

                    var list = conn.Query<ManagerMemberDto>(query).ToList();
                    return list;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 承接部门
        /// </summary>
        /// <returns></returns>
        public List<ShowDepartmentDto> Showbumen()
        {
            try
            {
                using(var conn=new SqlConnection(_configuration.GetConnectionString("MSSQL")))
                {
                    var list = "select a.StaffId as StaffId,a.StaffName as StaffName,b.DeptId as DeptId, b.DeptName as DeptName from Staff a join Department b on a.DeptId=b.DeptId where 1=1  ";
                    var query = conn.Query<ShowDepartmentDto>(list).ToList();
                    return query;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 修改经理
        /// </summary>
        /// <param name="managerSet"></param>
        /// <returns></returns>
        public int PostAddManger( DepartmentManagerSet managerSet)
        {
            try
            {
                _hongStoneContext.Entry(managerSet).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }
        /// <summary>
        /// 修改总经理
        /// </summary>
        /// <param name="managerZhong"></param>
        /// <returns></returns>
        public int PostZAddManger(DepartmentManagerZhong managerZhong)
        {
            try
            {
                _hongStoneContext.Entry(managerZhong).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 删除经理
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Deletjinli(int id)
        {
            try
            {
                
               var list= _hongStoneContext.departmentManagerSets.FirstOrDefault(p=>p.KDepartmentId==id);
               if(list!=null)
                {
                    list.KDepartmentManagerId = 0;
                }
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 删除总经理
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Deletzjinli(int id)
        {
            try
            {

                var list = _hongStoneContext.departmentManagerZhongs.FirstOrDefault(p => p.KDepartmentIdzhong == id);
                if (list != null)
                {
                    list.KalwaysManagerId = 0;
                }
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 假日添加
        /// </summary>
        /// <param name="dayOff"></param>
        /// <returns></returns>
        public int AddDayOffA(DayOffTable dayOff)
        {
            try
            {
                _hongStoneContext.dayOffTables.Add(dayOff);
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 特殊假日添加
        /// </summary>
        /// <param name="dayOff"></param>
        /// <returns></returns>
        public int AddSpecialDayOffB(SpecialDayOffTable specialDay)
        {
            try
            {
                _hongStoneContext.specialDayOffTables.Add(specialDay);
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 假日 显示 查询
        /// </summary>
        /// <returns></returns>
        
        public List<TimeJiaDto> ShowDayOffA(int? timee)
        {
            try
            {
                using(var conn=new SqlConnection(_configuration.GetConnectionString("MSSQL")))
                {
                    var list = "select DayOffId,Substring(CONVERT(nvarchar(20),DayOffTime),1,10)as DayOffTime from DayOffTable where 1=1 ";
                    if (timee > 0)
                    {
                        list += $" and DATENAME(YEAR,DayOffTime) ='{timee}'";
                    }
                    var query = conn.Query<TimeJiaDto>(list).ToList();
                    return query;
                }
                    
            }
            catch (Exception)
            {

                throw;
            }
        }
        /// <summary>
        /// 特殊假日 显示 查询
        /// </summary>
        /// <returns></returns>
        public List<TimeJiaDto> ShowSpecialDayOffB(int? times)
        {
            try
            {
                using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
                {
                    var list = "select SpecialDayOffId,Substring(CONVERT(nvarchar(20),SpecialDayOffTime),1,10)  as SpecialDayOffTime from SpecialDayOffTable where 1=1  ";
                    if(times>0)
                    {
                        list += $" and DATENAME(YEAR,SpecialDayOffTime)='{times}' ";
                    }
                    var query = conn.Query<TimeJiaDto>(list).ToList();
                    return query;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 假日 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DateDayOffA(int id)
        {
            try
            {
             var list=  _hongStoneContext.dayOffTables.Find(id);
                _hongStoneContext.Remove(list);
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }


        /// <summary>
        /// 特殊假日 特殊工作日 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DateSpecialDayOffB(int id)
        {
            try
            {
                var list = _hongStoneContext.specialDayOffTables.Find(id);
                _hongStoneContext.Remove(list);
                return _hongStoneContext.SaveChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

       /// <summary>
       /// 上班 时间
       /// </summary>
       /// <param name="year"></param>
       /// <param name="name"></param>
       /// <param name="pumen"></param>
       /// <returns></returns>
        public List<AttendanceMessageDto> ShowShangTime(int? year,string name,int? pumen)
        {
            try
            {
                using(var conn=new SqlConnection(_configuration.GetConnectionString("MSSQL")))
                {
                    var list = "select b.DeptId , SerialNumber,c.DeptName,b.StaffName,Yearfen,UpAnd, max(case when Timejibie = 1  then shiTime else '' end) as 'timeyi' ,max(case when Timejibie = 2  then shiTime else '' end) as 'timeer',max(case when Timejibie = 3  then shiTime else '' end) as 'timesan',max(case when Timejibie = 4  then shiTime else '' end) as 'timesi',max(case when Timejibie = 5  then shiTime else '' end) as 'timewu',max(case when Timejibie = 6  then shiTime else '' end) as 'timeliu',max(case when Timejibie = 7  then shiTime else '' end) as 'timeqi',max(case when Timejibie = 8  then shiTime else '' end) as 'timeba',max(case when Timejibie = 9  then shiTime else '' end) as 'timejiu',max(case when Timejibie = 10 then shiTime else '' end) as 'timeshi',max(case when Timejibie = 11 then shiTime else '' end) as 'timeshiyi' ,max(case when Timejibie = 12 then shiTime else '' end) as 'timeshier'from AttendanceMessage a   join Staff b on a.staffMsId = b.StaffId join Department c on b.DeptId = c.DeptId where UpAnd = 1  ";
                    if(year>0)
                    {
                        list += $" and Yearfen='{year}' ";
                    }
                    if(!string.IsNullOrEmpty(name))
                    {
                        list += $" and  b.StaffName like '%{name}%'";
                    }
                    if(pumen>0)
                    {
                        list += $" and  b.DeptId = '{pumen}'";
                    }
                     list+= " group by b.DeptId, SerialNumber,c.DeptName,b.StaffName,Yearfen,UpAnd";
                    var query = conn.Query<AttendanceMessageDto>(list).ToList();
                    return query;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 下班 时间
        /// </summary>
        /// <param name="year"></param>
        /// <param name="name"></param>
        /// <param name="pumen"></param>
        /// <returns></returns>
        public List<AttendanceMessageDto> ShowXiaTime(int? year, string name, int? pumen)
        {
            try
            {
                using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
                {
                    var list = " select  b.DeptId , SerialNumber,c.DeptName,b.StaffName,Yearfen,UpAnd, max(case when Timejibie = 13 then shiTime else '' end) as 'timeshisan' ,max(case when Timejibie = 14 then shiTime else '' end) as 'timeshisi' ,max(case when Timejibie = 15 then shiTime else '' end) as 'timeshiwu' , max(case when Timejibie = 16 then shiTime else '' end) as 'timeshiliu' , max(case when Timejibie = 17 then shiTime else '' end) as 'timeshiqi' , max(case when Timejibie = 18 then shiTime else '' end) as 'timeshiba' , max(case when Timejibie = 19 then shiTime else '' end) as 'timeshijiu' , max(case when Timejibie = 20 then shiTime else '' end) as 'timeershi' , max(case when Timejibie = 21 then shiTime else '' end) as 'timeeryi' , max(case when Timejibie = 22 then shiTime else '' end) as 'timeerer' , max(case when Timejibie = 23 then shiTime else '' end) as 'timeersan' , max(case when Timejibie = 24 then shiTime else '' end) as 'timeersi' from AttendanceMessage a join Staff b on a.staffMsId = b.StaffId join Department c on b.DeptId = c.DeptId where UpAnd = 2  " ;
                    if (year > 0)
                    {
                        list += $" and Yearfen='{year}' ";
                    }
                    if (!string.IsNullOrEmpty(name))
                    {
                        list += $" and  b.StaffName like '%{name}%'";
                    }
                    if (pumen > 0)
                    {
                        list += $" and  b.DeptId = '{pumen}'";
                    }
                    list += " group by b.DeptId, SerialNumber,c.DeptName,b.StaffName,Yearfen,UpAnd";
                    var query = conn.Query<AttendanceMessageDto>(list).ToList();
                    return query;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 年假显示
        /// </summary>
        /// <param name="bum"></param>
        /// <param name="yearj"></param>
        /// <returns></returns>
        public List<AnnualLeaveDto> ShowAnnual(int? bum,int? yearj)
        {
            try
            {
                using(var conn=new SqlConnection(_configuration.GetConnectionString("MSSQL")))
                {
                    var list = " select a.AnnualId as AnnualId,a.NumberHao as NumberHao,b.StaffName as StaffName,a.Tianshu as Tianshu,a.Year as Year,b.DeptId as DeptId from AnnualLeave a join Staff b on a.StaffIdAy=b.StaffId where 1=1 ";
                    if(bum>0)
                    {
                        list += $" and b.DeptId = '{bum}'";
                    }
                    if(yearj>0)
                    {
                        list += $" and a.Year = '{yearj}'";
                    }
                    var query = conn.Query<AnnualLeaveDto>(list).ToList();
                    return query;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 年假设置 
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public int HolidayTime(List<AnnualLeave>  ids)
        {
            try
            {
                var list = 0;
                using (var conn=new SqlConnection(_configuration.GetConnectionString("MSSQL")))
                {
                    foreach (var item in ids)
                    {
                      list = conn.Execute($"update AnnualLeave set Tianshu={item.Tianshu} where AnnualId in ({item.AnnualId})");
                    }
                    return list;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }


        /// <summary>
        /// 导入 上传 Excel 特殊假日
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int Uploading(List<SpecialDayOffTable> user)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var list = conn.BulkInsert(user);
                if (list != null)
                {
                    return 1;
                }
                else
                {
                    return 0;
                }
            }
        }

    }
}
